package com.cube.poiexcel.controller;

import cn.afterturn.easypoi.cache.manager.POICacheManager;
import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
import cn.afterturn.easypoi.excel.entity.ExcelToHtmlParams;
import com.cube.poiexcel.util.ExcelUtilxlsx;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;

/**
 * <p>Title: DateExportController</p>
 * <p>description: </p>
 * <p>Company: </p>
 *
 * @author ZT
 * @date 2021/5/23 15:53
 **/
@RestController
@Slf4j
@AllArgsConstructor
@RequestMapping(value = "/template")
public class DataExportController {

//	private RedisTemplate redisTemplate;

    /**
     * excel模板下载
     * <p>
     * ps：目前未调用，模版放在前端的
     *
     * @param response
     * @param request
     * @return
     * @author zt
     */
    @RequestMapping(value = "/infotemplate", produces = "text/html;charset=UTF-8")
    public void downLoadDeviceInfoExcelTemplate(HttpServletResponse response, HttpServletRequest request) {
        //获取评估表excel模板的保存地址，即resources根目录下
        InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("tmp2.xlsx");
        //文件输入流
        XSSFWorkbook wb = null;
        //使用XSSFWorkbook对象读取excel文件
        try {
            wb = new XSSFWorkbook(resourceAsStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //设置contentType为vnd.ms-excel
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");

        // 对文件名进行处理，防止文件名乱码。可在此处修改要下载的文件名称
        String fileName = "随量费用月度报表.xlsx";
        //浏览器设置
        String userAgent = request.getHeader("User-Agent");
        try {
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                //IE浏览器处理
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        // Content-disposition属性设置成以附件方式进行下载
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        //调取response对象中的OutputStream对象
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 客户数据-评估表excel下载
     * ！！！！！！！！！！复制列操作
     * <p>
     * ps：目前未调用，模版放在前端的
     *
     * @param response
     * @param request
     * @return
     * @author zt
     */
//	@PostMapping("/export")
    @RequestMapping(value = "/export", produces = "text/html;charset=UTF-8")
//@RequestBody BasicInfo basicInfo, @RequestBody List<AnswerRecord> answerRecords,
    public String export(HttpServletResponse response, HttpServletRequest request) {
//		BasicInfo basicInfo = new BasicInfo();
//		List<AnswerRecord> answerRecords= new ArrayList<>();
//		basicInfo.setEvaluationNumber("4546789");
        //获取评估表excel模板的保存地址，即resources根目录下
        InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("newt.xlsx");
        //文件输入流
        XSSFWorkbook wb = null;

////		//在redis里取值，看是否有
////		RedisUtil<String, String> redisUtil = new RedisUtil(redisTemplate);
////		String ievaluationVO = redisUtil.get("export:evaluationVO");
////		//如果有的话，直接返回
////		EvaluationVO evaluationVO = new EvaluationVO();
//		BasicInfo basicInfo = new BasicInfo();
//		List<AnswerRecord> answerRecords= new ArrayList<>();
////		if(StringUtils.isNotBlank(ievaluationVO)) {
////			evaluationVO = JSONObject.parseObject(ievaluationVO, EvaluationVO.class);
////			answerRecords=evaluationVO.getAnswerRecords();
////			basicInfo=evaluationVO.getBasicInfo();
////		}
//
//		if(evaluationVO!=null){
//			answerRecords=evaluationVO.getAnswerRecords();
//			basicInfo=evaluationVO.getBasicInfo();
//		}
//		//对象转map，下划线，去null
//		//设置匹配map
//		HashMap<String, String> values = new HashMap<String, String>();
//		if(basicInfo!=null||answerRecords!=null) {
//			if (basicInfo != null) {
//				Map map = HumpTransformUnderlineUtils.transform(basicInfo);
//				Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
//				while (it.hasNext()) {
//					Map.Entry<String, String> entry = it.next();
//					values.put(entry.getKey(), entry.getValue());
//				}
//			}
//			if (answerRecords != null) {
//				for (AnswerRecord answerRecord : answerRecords) {
//					String inputText = answerRecord.getInputText();
//					String selectId = answerRecord.getSelectId();
//					String value;
//					String key = String.valueOf(answerRecord.getQuestionId());
//					if (key != null) {
//						if (StringUtils.isNotBlank(inputText)) {
//							value = inputText;
//						} else if (StringUtils.isNotBlank(selectId)) {
//							value = selectId;
//						} else {
//							continue;
//						}
//						values.put(key, value);
//					}
//				}
//			}
//		}

        //使用XSSFWorkbook对象读取excel文件
        try {
            wb = new XSSFWorkbook(resourceAsStream);

            int numberOfSheets = wb.getNumberOfSheets();

            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = wb.getSheetAt(i);
                sheet.setForceFormulaRecalculation(true);
                if (sheet == null) {
                    continue;
                }
                int rowNum = sheet.getLastRowNum();
                for (int j = 0; j < rowNum; j++) {
                    XSSFRow row = sheet.getRow(j);

                    if (row == null) {
                        continue;
                    }
                    short cellNum = row.getLastCellNum();
                    for (short k = 0; k < cellNum; k++) {
                        XSSFCell cell = row.getCell(k);
                        if (cell == null) {
                            continue;
                        }
                        CellType cellTypeEnum = cell.getCellType();

                        if (CellType.STRING == cellTypeEnum) {
                            String value = cell.getStringCellValue();
                            if (StringUtils.isNotBlank(value)) {
                                int i1 = value.indexOf("#", 0);
                                int i2 = value.lastIndexOf("#");
                                if (i1 == -1 || i2 == -1) {
                                    continue;
                                }


//								row.shiftCellsLeft(k,k,1);
//								POIUtil2.copyRows(0,0,1,sheet);
//								k++;
                                //复制列操作
                                ExcelUtilxlsx.copycols(sheet, k, k + 1, k + 2, true);
//								ExcelUtilxlsx.copycols(sheet,k,sheet.getRow(0).getPhysicalNumberOfCells()-1,sheet.getRow(0).getPhysicalNumberOfCells(),true);
                                k++;
                                String key = StringUtils.substring(value, i1 + 1, i2);
                                String s1 = "";
//								if(values!=null&&values.get(key)!=null){
//									s1= values.get(key);
//								}else {
//									s1="";
//								}
                                if ("null".equals(s1)) {
                                    s1 = "";
                                }
                                if ("-1".equals(s1)) {
                                    s1 = "";
                                }
                                s1 = "789";
                                String s = StringUtils.replaceOnce(value, "#" + key + "#", s1);
                                Double s2 = Double.valueOf(7890);
                                cell.setCellValue(s2);
                                cell.setCellType(CellType.NUMERIC);
                            }
                        }

                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        //设置contentType为vnd.ms-excel
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");

        // 对文件名进行处理，防止文件名乱码。可在此处修改要下载的文件名称
        String fileName = "随量费用月度报表.xlsx";
        //浏览器设置
        String userAgent = request.getHeader("User-Agent");
        try {
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                //IE浏览器处理
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        // Content-disposition属性设置成以附件方式进行下载
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        //调取response对象中的OutputStream对象
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "下载完即可关闭此页";
    }

    /**
     * 07 版本EXCEL预览
     */
    @RequestMapping("07")
    public void toHtmlOf07Base(HttpServletResponse response) throws IOException, InvalidFormatException {
        ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile("testExportTitleExcel.xlsx")));
        response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());
    }

    /**
     * 03 版本EXCEL预览
     */
    @RequestMapping("03img")
    public void toHtmlOf03Img(HttpServletResponse response) throws IOException, InvalidFormatException {
        ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile("tmp3.xls")), true, "yes");
        response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());
    }

//	/**
//	 * 临时存储评估表结果到redis，用来打印
//	 */
//	@PostMapping("/save/redis")
//	@ApiOperationSupport(order = 2)
//	@ApiOperation(value = "临时存储评估表结果到redis，用来打印", notes = "临时存储评估表结果到redis，用来打印")
//	public R saveRedis(@RequestBody EvaluationVO evaluationVO) {
////		EvaluationVO evaluationVO = new EvaluationVO();
//		RedisUtil<String, String> redisUtil = new RedisUtil(redisTemplate);
////
////		if (basicInfo != null || answerRecords != null) {
////			if (basicInfo != null) {
////				evaluationVO.setBasicInfo(basicInfo);
////			}
////			if (answerRecords != null) {
////				evaluationVO.setAnswerRecords(answerRecords);
////			}
////		}else {return R.fail("评估表内容不可为空。");}
//
//		//转换存储对象
//		String ievaluationVO = JSON.toJSONString(evaluationVO);
//		redisUtil.set("export:evaluationVO",ievaluationVO,100);
//		return R.success("成功存储到redis。");
//	}

}
